package com.psct.data;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.psct.bean.Criticism;
import com.psct.bean.TextData;

/**
 * 课文数据库
 */
public class PSCTDatabase {
	private static final String TAG = "Database";
	private SQLiteDatabase mDatabase;

	private static final String DATABASE_NAME = "chinese_text";
	private static final int DATABASE_VERSION = 1;
	/**
	 * 课文表名
	 */
	public static final String TABLE_TEXT = "BookTbl";
	public static final String KEY_TITLE = "title";
	public static final String KEY_BOOKID = "bookid";
	public static final String KEY_CRITICISM_COUNT = "criticismCount";
	/**
	 * 图片表
	 */
	public static final String TABLE_IMAGE = "ImageTbl";
	public static final String KEY_IMAGE_PATH = "ImagePath";
	/**
	 * 评论表
	 */
	public static final String TABLE_CRITICISM = "criticismTBL";
	public static final String KEY_TEXTID = "textid";
	public static final String KEY_CONTENT = "content";
	public static final String KEY_TIME = "time";

	private final TextOpenHelper mDatabaseOpenHelper;

	/**
	 * 构造方法
	 * 
	 * @param
	 */
	public PSCTDatabase(Context context) {
		mDatabaseOpenHelper = new TextOpenHelper(context);
	}

	/**
	 * 打开数据库
	 */
	private void open() {
		if (mDatabase == null || !mDatabase.isOpen())
			mDatabase = mDatabaseOpenHelper.getWritableDatabase();
	}

	/**
	 * 关闭数据库
	 */
	public void close() {
		if (mDatabase != null && mDatabase.isOpen())
			mDatabase.close();
		mDatabase = null;
	}

	/**
	 * 增加一条课文记录
	 * 
	 * @return 插入的id 如果失败返回-1
	 */
	public long addText(String title, int book) {
		open();
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_TITLE, title);
		initialValues.put(KEY_BOOKID, book);

		long count = mDatabase.insert(TABLE_TEXT, null, initialValues);
		close();
		return count;
	}

	public long addData(int bookId, List<TextData> data) {
		if (data == null || data.size() < 1) {
			Log.v(TAG, "插入的数据为空！！！");
			return -1;
		}
		open();
		mDatabase.beginTransaction();
		int count = 0;
		for (TextData text : data) {
			ContentValues values = new ContentValues();
			values.put(KEY_BOOKID, bookId);
			values.put(KEY_TITLE, text.getTitle());
			values.put(KEY_TEXTID, text.getId());
			values.put(KEY_CRITICISM_COUNT, text.getCriticismCount());
			long textId = mDatabase.insert(TABLE_TEXT, null, values);
			Log.v(TAG, "插入一条:bookId=" + bookId + ",title:" + text.getTitle());
			List<String> imagePath = text.getImagePath();
			if (imagePath != null && imagePath.size() > 0) {
				for (String path : imagePath) {
					ContentValues values1 = new ContentValues();
					values1.put(KEY_TEXTID, textId);
					values1.put(KEY_IMAGE_PATH, path);
					mDatabase.insert(TABLE_IMAGE, null, values1);
				}

			}
			List<Criticism> criticismList = text.getCriticism();
			if (criticismList != null && criticismList.size() > 0) {
				for (Criticism c : criticismList) {
					ContentValues values2 = new ContentValues();
					values2.put(KEY_TEXTID, textId);
					values2.put(KEY_CONTENT, c.content);
					// values2.put(KEY_TIME, c.date.getTime());
					mDatabase.insert(TABLE_CRITICISM, null, values2);
				}
			}
			count++;
		}
		mDatabase.setTransactionSuccessful();
		mDatabase.endTransaction();
		mDatabase.close();
		return count;
	}

	public List<TextData> querryTextAll(){
		List<TextData> book = new ArrayList<TextData>();
		open();
		String[] columns = new String[] { "_id", KEY_CRITICISM_COUNT,KEY_TITLE, KEY_BOOKID, KEY_TEXTID };
		Cursor cursor = mDatabase.query(TABLE_TEXT, null, null, null, null, null,
				null);
		int count = cursor.getCount();
		cursor.moveToFirst();
		for (int i = 0; i < count; i++) {
			TextData temp = new TextData();
			temp.setBookId(Integer.valueOf(cursor.getString(cursor.getColumnIndexOrThrow(KEY_BOOKID))));
			temp.setTitle(cursor.getString(cursor.getColumnIndexOrThrow(KEY_TITLE)));
			temp.setId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_TEXTID)));
			temp.setCriticismCount(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_CRITICISM_COUNT)));
			book.add(temp);
			cursor.moveToNext();
		}
		cursor.close();
		this.close();
		return book;
	}
	
	/**
	 * 根据课本id查询课文
	 * 
	 * @param index
	 * @return 课文列表，如果没有数据 则为空list
	 */
	public List<TextData> querryTextByBookId(int index) {
		List<TextData> book = new ArrayList<TextData>();
		open();
		String[] columns = new String[] { "_id", KEY_CRITICISM_COUNT,KEY_TITLE, KEY_BOOKID, KEY_TEXTID };
		String selection = KEY_BOOKID + " =?";
		String selectionArgs[] = new String[] { String.valueOf(index) };
		Cursor cursor = mDatabase.query(TABLE_TEXT, columns, selection, selectionArgs, null, null,
				null);
		int count = cursor.getCount();
		cursor.moveToFirst();
		for (int i = 0; i < count; i++) {
			TextData temp = new TextData();
			temp.setBookId(index);
			temp.setTitle(cursor.getString(cursor.getColumnIndexOrThrow(KEY_TITLE)));
			temp.setId(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_TEXTID)));
			temp.setCriticismCount(cursor.getInt(cursor.getColumnIndexOrThrow(KEY_CRITICISM_COUNT)));
			book.add(temp);
			cursor.moveToNext();
		}
		cursor.close();
		this.close();
		return book;
	}

	/**
	 * 根据课文id查询图片集合
	 * 
	 * @return
	 */
	public List<String> querryImagePathByTextID(int id) {
		List<String> temp = new ArrayList<String>();
		open();
		String[] columns = new String[] { "_id", KEY_TEXTID, KEY_IMAGE_PATH };
		String selection = KEY_TEXTID + " =?";
		String selectionArgs[] = new String[] { String.valueOf(id) };
		Cursor cursor = mDatabase.query(TABLE_IMAGE, columns, selection, selectionArgs, null, null,
				null);
		int count = cursor.getCount();
		cursor.moveToFirst();
		for (int i = 0; i < count; i++) {
			temp.add(cursor.getString(cursor.getColumnIndexOrThrow(KEY_IMAGE_PATH)));
			cursor.moveToNext();
		}
		cursor.close();
		mDatabase.close();
		return temp;
	}

	/**
	 * 像数据库添加图片地址
	 * 
	 * @param urlList
	 */
	public void addImagePath(int textId, List<String> urlList) {
		// TODO Auto-generated method stub
		open();
		mDatabase.beginTransaction();
		for (String path : urlList) {
			ContentValues values1 = new ContentValues();
			values1.put(KEY_TEXTID, textId);
			values1.put(KEY_IMAGE_PATH, path);
			mDatabase.insert(TABLE_IMAGE, null, values1);
		}
		mDatabase.setTransactionSuccessful();
		mDatabase.endTransaction();
		close();
	}

	/**
	 * 根据表名清除一张表
	 * 
	 * @param TableName
	 * @return
	 */
	public int clearTbl(String TableName) {
		open();
		int count = mDatabase.delete(TableName, null, null);
		this.close();
		return count;
	}

	/**
	 * 创建打开数据库帮助内部类
	 */
	private static class TextOpenHelper extends SQLiteOpenHelper {

		private static final String CREATBOOKTBL = "create table " + TABLE_TEXT
				+ "(_id integer primary key autoincrement," + KEY_TITLE + " text," + KEY_TEXTID
				+ " integer," + KEY_CRITICISM_COUNT + " integer," + KEY_BOOKID + " integer)";
		private static final String CREATCRITICISM = "create table " + TABLE_CRITICISM
				+ "(_id integer primary key autoincrement," + KEY_TEXTID + " integer," + KEY_TIME
				+ " text," + KEY_CONTENT + " text)";
		private static final String CREATIMAGE = "create table " + TABLE_IMAGE
				+ "(_id integer primary key autoincrement," + KEY_TEXTID + " integer,"
				+ KEY_IMAGE_PATH + " text)";

		TextOpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATBOOKTBL);
			db.execSQL(CREATCRITICISM);
			db.execSQL(CREATIMAGE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "数据库版本从 " + oldVersion + " 更新到 " + newVersion + ", 这会删除以前所有的数据");
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_TEXT);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_CRITICISM);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_IMAGE);
			onCreate(db);
		}
	}

}
